package com.nekonomics
package persistence.query

import domain.detail.SkuSpecDetail

import doobie.*
import doobie.implicits.*
import zio.*

trait SkuSpecDetailRepo {
  def listSpecsBySkuId(skuId: Long): ConnectionIO[List[SkuSpecDetail]]
}

object SkuSpecDetailRepo {
  val live: ULayer[SkuSpecDetailRepo] = ZLayer.succeed(new SkuSpecDetailRepoLive)
}


class SkuSpecDetailRepoLive extends SkuSpecDetailRepo {


  //todo sql优化
  override def listSpecsBySkuId(skuId: Long): ConnectionIO[List[SkuSpecDetail]] = {
    sql"""
         select
          spec_name.id as specId, spec_value.id as specValueId, spec_name.name as name, spec_name.category_id as categoryId, spec_value."value" as "value"
         from sku_spec_value
         join spec_value on sku_spec_value.spec_value_id = spec_value.id
         join spec_name on spec_value.spec_id = spec_name.id
         where sku_spec_value.sku_id = $skuId
       """
      .query[SkuSpecDetail]
      .to[List]
  }


}